//The MIT License
//
//Copyright (c) 2009 nodchip
//
//Permission is hereby granted, free of charge, to any person obtaining a copy
//of this software and associated documentation files (the "Software"), to deal
//in the Software without restriction, including without limitation the rights
//to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
//copies of the Software, and to permit persons to whom the Software is
//furnished to do so, subject to the following conditions:
//
//The above copyright notice and this permission notice shall be included in
//all copies or substantial portions of the Software.
//
//THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
//IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
//FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
//AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
//LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
//OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
//THE SOFTWARE.
package tv.dyndns.kishibe.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import tv.dyndns.kishibe.client.Constant;
import tv.dyndns.kishibe.client.packet.PacketBbsResponse;
import tv.dyndns.kishibe.client.packet.PacketBbsThread;
import tv.dyndns.kishibe.client.packet.PacketChatData;
import tv.dyndns.kishibe.client.packet.PacketLinkData;
import tv.dyndns.kishibe.client.packet.PacketProblemCreationLog;
import tv.dyndns.kishibe.client.packet.PacketProblemData;
import tv.dyndns.kishibe.client.packet.PacketProblemDataMinimum;
import tv.dyndns.kishibe.client.packet.PacketRankingData;
import tv.dyndns.kishibe.client.packet.PacketUserData;
import tv.dyndns.kishibe.client.packet.PacketWrongAnswer;
import tv.dyndns.kishibe.server.ProblemManager.PacketProblemDataMinimums;
import tv.dyndns.kishibe.server.relevance.SentencesProcessor;

public class DirectDatabase implements Database {
	private final DatabaseConnectionPool connectionPool = new DatabaseConnectionPool();

	public interface Task<T> {
		public T run(Connection connection) throws SQLException;
	}

	private <T> T doTask(Task<T> task) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();

			PreparedStatement statement = connection.prepareStatement("START TRANSACTION");
			statement.execute();
			statement.close();

			final T result = task.run(connection);

			statement = connection.prepareStatement("COMMIT");
			statement.execute();
			statement.close();

			return result;

		} catch (Exception e) {
			try {
				PreparedStatement statement = connection.prepareStatement("ROLLBACK");
				statement.execute();
				statement.close();

			} catch (Exception e2) {
				e2.printStackTrace();
			}

			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (Exception e2) {
			}
			connection = null;
		}

		return null;
	}

	@Override
	public void addProblemIdsToReport(final int userCode, final int[] problemIds) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("INSERT IGNORE INTO report_problem (USER_CODE, PROBLEM_ID) VALUES (?, ?)");
				statement.setInt(1, userCode);
				for (int problemId : problemIds) {
					statement.setInt(2, problemId);
					statement.addBatch();
				}
				statement.executeBatch();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public void removeProblemIdFromReport(final int userCode, final int problemID) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("DELETE IGNORE FROM report_problem WHERE USER_CODE = ? AND PROBLEM_ID = ?");
				statement.setInt(1, userCode);
				statement.setInt(2, problemID);
				statement.executeUpdate();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public void clearProblemIdFromReport(final int userCode) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("DELETE IGNORE FROM report_problem WHERE USER_CODE = ?");
				statement.setInt(1, userCode);
				statement.executeUpdate();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public PacketProblemData[] getUserProblemReport(final int userCode) {
		return doTask(new Task<PacketProblemData[]>() {
			@Override
			public PacketProblemData[] run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("SELECT * FROM problem WHERE ID IN (SELECT PROBLEM_ID FROM report_problem WHERE USER_CODE = ?)");
				statement.setInt(1, userCode);
				return getPacketProblemDatas(statement);
			}
		});
	}

	private PacketProblemData[] getPacketProblemDatas(PreparedStatement statement) throws SQLException {
		final ResultSet resultSet = statement.executeQuery();
		resultSet.last();
		int numberOfRow = Math.max(resultSet.getRow(), 0);
		resultSet.beforeFirst();

		final PacketProblemData[] datas = new PacketProblemData[numberOfRow];
		for (int index = 0; resultSet.next() && index < numberOfRow; ++index) {
			final PacketProblemData data = new PacketProblemData();
			data.problemId = resultSet.getInt("problem.ID");
			data.sentence = resultSet.getString("problem.SENTENCE");
			data.note = resultSet.getString("problem.NOTE");
			data.good = resultSet.getInt("problem.GOOD");
			data.bad = resultSet.getInt("problem.BAD");
			data.genre = resultSet.getInt("problem.GENRE");
			data.type = resultSet.getInt("problem.TYPE");
			data.randomFlag = resultSet.getInt("problem.RANDOM_FLAG");
			data.answer = new String[4];
			data.answer[0] = resultSet.getString("problem.ANSWER0");
			data.answer[1] = resultSet.getString("problem.ANSWER1");
			data.answer[2] = resultSet.getString("problem.ANSWER2");
			data.answer[3] = resultSet.getString("problem.ANSWER3");
			data.creator = resultSet.getString("problem.CREATER");
			data.voteGood = resultSet.getInt("problem.VOTE_GOOD");
			data.voteBad = resultSet.getInt("problem.VOTE_BAD");
			datas[index] = data;
		}
		resultSet.close();
		statement.close();
		return datas;
	}

	@Override
	public PacketUserData getUserData(final int userCode) {
		return doTask(new Task<PacketUserData>() {
			@Override
			public PacketUserData run(Connection connection) throws SQLException {
				PreparedStatement statement = connection.prepareStatement("SELECT * FROM player WHERE USER_CODE = ?");
				statement.setInt(1, userCode);
				ResultSet rs = statement.executeQuery();
				final PacketUserData data = new PacketUserData();
				data.initialize();
				while (rs.next()) {
					data.userCode = rs.getInt("USER_CODE");
					data.playerName = rs.getString("NAME");
					data.greeting = rs.getString("GREETING");
					data.highScore = rs.getInt("HIGH_SCORE");
					data.averageScore = rs.getInt("AVERAGE_SCORE");
					data.playCount = rs.getInt("PLAY_COUNT");
					data.rating = rs.getInt("VICTORY_POINT");
					data.levelName = rs.getInt("LEVEL_NAME");
					data.levelNumber = rs.getInt("LEVEL_NUMBER");
					data.averageRank = rs.getFloat("AVERAGE_RANK");
					data.genre = rs.getInt("GENRE");
					data.type = rs.getInt("TYPE");
					data.classLevel = rs.getInt("CLASS_LEVEL");
					data.imageFileName = rs.getString("IMAGE_FILE_NAME");
					data.playSound = rs.getBoolean("PLAY_SOUND");
					data.multiGenre = rs.getBoolean("MULTI_GENRE");
					data.multiType = rs.getBoolean("MULTI_TYPE");
					data.difficultSelect = rs.getInt("DIFFICULT_SELECT");
					data.rankingMove = rs.getBoolean("RANKING_MOVE");
					data.bbsDispInfo = rs.getInt("BBS_DISP_INFO");
					data.bbsAge = rs.getBoolean("BBS_AGE");
					data.timerMode = rs.getInt("TIMER_MODE");
					data.prefecture = rs.getInt("PREFECTURE");
					data.chat = rs.getBoolean("CHAT");
					data.newProblem = rs.getBoolean("NEW_PROBLEM");
					data.publicEvent = rs.getBoolean("PUBLIC_EVENT");
					data.hideAnswer = rs.getBoolean("HIDE_ANSWER");
				}
				rs.close();
				statement.close();

				// 個人正解率
				data.correctCount = new int[Constant.NUMBER_OF_GENRE][Constant.NUMBER_OF_TYPE_WITH_RANDOM][2];

				statement = connection.prepareStatement("SELECT * FROM correct_count WHERE USER_CODE = ?");
				statement.setInt(1, data.userCode);
				rs = statement.executeQuery();
				while (rs.next()) {
					final int genre = rs.getInt("GENRE");
					final int type = rs.getInt("TYPE");
					final int good = rs.getInt("GOOD");
					final int bad = rs.getInt("BAD");
					if (genre >= Constant.NUMBER_OF_GENRE || type >= Constant.NUMBER_OF_TYPE_WITH_RANDOM) {
						continue;
					}
					data.correctCount[genre][type][0] = good;
					data.correctCount[genre][type][1] = bad;
				}
				rs.close();
				statement.close();

				// 無視ユーザーコード
				statement = connection.prepareStatement("SELECT TARGET_USER_CODE FROM ignore_id WHERE USER_CODE = ?");
				statement.setInt(1, userCode);
				rs = statement.executeQuery();
				data.ignoreUserCodes = new HashSet<Integer>();
				while (rs.next()) {
					final int targetUserCode = rs.getInt("TARGET_USER_CODE");
					data.ignoreUserCodes.add(targetUserCode);
				}
				data.ignoreUserCodes.addAll(DatabaseFactory.getInstance().getServerIgnoreUserCode());
				rs.close();
				statement.close();

				return data;
			}
		});
	}

	@Override
	public void setUserData(final PacketUserData data) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				PreparedStatement statement = connection.prepareStatement("REPLACE INTO player (USER_CODE, NAME, GREETING, HIGH_SCORE, AVERAGE_SCORE, PLAY_COUNT, VICTORY_POINT, LEVEL_NAME, LEVEL_NUMBER, AVERAGE_RANK, GENRE, TYPE, CLASS_LEVEL, IMAGE_FILE_NAME, PLAY_SOUND, MULTI_GENRE, MULTI_TYPE, DIFFICULT_SELECT, RANKING_MOVE, LAST_LOGIN, BBS_DISP_INFO, BBS_AGE, TIMER_MODE, PREFECTURE, CHAT, NEW_PROBLEM, PUBLIC_EVENT, HIDE_ANSWER) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
				statement.setInt(1, data.userCode);
				statement.setString(2, data.playerName != null ? data.playerName : "");
				statement.setString(3, data.greeting != null ? data.greeting : "");
				statement.setInt(4, data.highScore);
				statement.setInt(5, data.averageScore);
				statement.setInt(6, data.playCount);
				statement.setInt(7, data.rating);
				statement.setInt(8, data.levelName);
				statement.setInt(9, data.levelNumber);
				statement.setFloat(10, data.averageRank);
				statement.setInt(11, data.genre);
				statement.setInt(12, data.type);
				statement.setInt(13, data.classLevel);
				statement.setString(14, (data.imageFileName != null && data.imageFileName.length() != 0) ? data.imageFileName : Constant.ICON_NO_IMAGE);
				statement.setBoolean(15, data.playSound);
				statement.setBoolean(16, data.multiGenre);
				statement.setBoolean(17, data.multiType);
				statement.setInt(18, data.difficultSelect);
				statement.setBoolean(19, data.rankingMove);
				statement.setTimestamp(20, new Timestamp(System.currentTimeMillis()));
				statement.setInt(21, data.bbsDispInfo);
				statement.setBoolean(22, data.bbsAge);
				statement.setInt(23, data.timerMode);
				statement.setInt(24, data.prefecture);
				statement.setBoolean(25, data.chat);
				statement.setBoolean(26, data.newProblem);
				statement.setBoolean(27, data.publicEvent);
				statement.setBoolean(28, data.hideAnswer);
				statement.executeUpdate();
				statement.close();

				statement = connection.prepareStatement("REPLACE INTO correct_count (USER_CODE, GENRE, TYPE, GOOD, BAD) VALUE (?, ?, ?, ?, ?)");
				statement.setInt(1, data.userCode);
				for (int genre = 0; genre < Constant.NUMBER_OF_GENRE; ++genre) {
					statement.setInt(2, genre);
					for (int type = 0; type < Constant.NUMBER_OF_TYPE_WITH_RANDOM; ++type) {
						statement.setInt(3, type);
						statement.setInt(4, data.correctCount[genre][type][0]);
						statement.setInt(5, data.correctCount[genre][type][1]);
						statement.addBatch();
					}
				}
				statement.executeBatch();
				statement.close();
				return null;
			}
		});
	}

	private static final String KEY_SESSION = "SESSION";
	private static final String KEY_PLAY = "PLAY";
	private static final String KEY_PAGE_VIEW = "page_view";

	@Override
	public PageView loadPageView() {
		return doTask(new Task<PageView>() {
			@Override
			public PageView run(Connection connection) throws SQLException {
				final PageView pageView = new PageView();
				final PreparedStatement statement = connection.prepareStatement("SELECT * FROM page_view");
				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					final String type = resultSet.getString("TYPE");
					final int number = resultSet.getInt("COUNT");

					if (type.equals(KEY_SESSION)) {
						pageView.session = number;
					} else if (type.equals(KEY_PLAY)) {
						pageView.play = number;
					} else if (type.equals(KEY_PAGE_VIEW)) {
						pageView.pageView = number;
					}
				}
				resultSet.close();
				statement.close();
				return pageView;
			}
		});
	}

	@Override
	public void savePageView(final PageView pageView) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("REPLACE INTO page_view (TYPE, COUNT) VALUES (?, ?)");

				statement.setString(1, KEY_SESSION);
				statement.setInt(2, pageView.session);
				statement.addBatch();

				statement.setString(1, KEY_PLAY);
				statement.setInt(2, pageView.play);
				statement.addBatch();

				statement.setString(1, KEY_PAGE_VIEW);
				statement.setInt(2, pageView.pageView);
				statement.addBatch();

				statement.executeBatch();

				statement.close();
				return null;
			}
		});
	}

	@Override
	public List<PacketProblemDataMinimum> getMinimumProblemDatas() {
		return doTask(new Task<List<PacketProblemDataMinimum>>() {
			@Override
			public List<PacketProblemDataMinimum> run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("SELECT ID, GENRE, TYPE, GOOD, BAD, RANDOM_FLAG FROM problem");

				final ResultSet resultSet = statement.executeQuery();
				final List<PacketProblemDataMinimum> datas = new ArrayList<PacketProblemDataMinimum>();
				while (resultSet.next()) {
					final PacketProblemDataMinimum data = new PacketProblemDataMinimum();
					data.problemId = resultSet.getInt("ID");
					data.genre = resultSet.getInt("GENRE");
					data.type = resultSet.getInt("TYPE");
					data.good = resultSet.getInt("GOOD");
					data.bad = resultSet.getInt("BAD");
					data.randomFlag = resultSet.getInt("RANDOM_FLAG");
					datas.add(data);
				}

				resultSet.close();
				statement.close();
				return datas;
			}
		});
	}

	@Override
	public PacketProblemDataMinimums getMinimumProblemDatasForThemeMode(final String[] queries) {
		return doTask(new Task<PacketProblemDataMinimums>() {
			@Override
			public PacketProblemDataMinimums run(Connection connection) throws SQLException {
				final Map<Integer, PacketProblemDataMinimum> problems = new HashMap<Integer, PacketProblemDataMinimum>();
				final PreparedStatement statement = connection.prepareStatement("SELECT * FROM problem WHERE MATCH (SENTENCE) AGAINST (?) ORDER BY ID ASC");
				for (String query : queries) {
					statement.setString(1, query);

					final ResultSet resultSet = statement.executeQuery();
					while (resultSet.next()) {
						final PacketProblemDataMinimum data = new PacketProblemDataMinimum();
						data.problemId = resultSet.getInt("ID");
						data.genre = resultSet.getInt("GENRE");
						data.type = resultSet.getInt("TYPE");
						data.good = resultSet.getInt("GOOD");
						data.bad = resultSet.getInt("BAD");
						data.randomFlag = resultSet.getInt("RANDOM_FLAG");
						problems.put(data.problemId, data);
					}

					resultSet.close();
				}

				statement.close();

				final PacketProblemDataMinimums problemDatas = new PacketProblemDataMinimums();
				problemDatas.addAll(problems.values());
				Collections.sort(problemDatas, new Comparator<PacketProblemDataMinimum>() {
					public int compare(PacketProblemDataMinimum o1, PacketProblemDataMinimum o2) {
						return o1.problemId - o2.problemId;
					}
				});
				return problemDatas;
			}
		});
	}

	private final Object lockAddProblemData = new Object();

	// 戻り値は問題番号
	@Override
	public int addProblemData(final PacketProblemData data) {
		synchronized (lockAddProblemData) {
			return doTask(new Task<Integer>() {
				@Override
				public Integer run(Connection connection) throws SQLException {
					final String[] answer = new String[4];
					final String[] choice = new String[4];
					for (int i = 0; i < 4; ++i) {
						answer[i] = choice[i] = "";

						if (data.answer != null && i < data.answer.length && data.answer[i] != null) {
							answer[i] = data.answer[i];
						}
						if (data.choice != null && i < data.choice.length && data.choice[i] != null) {
							choice[i] = data.choice[i];
						}
					}

					PreparedStatement statement = connection.prepareStatement("SELECT MAX(ID) FROM problem");
					final ResultSet resultSet = statement.executeQuery();
					int problemId = Integer.MIN_VALUE;
					while (resultSet.next()) {
						problemId = resultSet.getInt(1) + 1;
					}
					resultSet.close();
					statement.close();
					data.problemId = problemId;

					statement = connection.prepareStatement("INSERT INTO problem (ID, GENRE, TYPE, SENTENCE, ANSWER0, ANSWER1, ANSWER2, ANSWER3, CHOICE0, CHOICE1, CHOICE2, CHOICE3, GOOD, BAD, CREATER, NOTE, IMAGE_ANSWER, IMAGE_CHOICE, RANDOM_FLAG) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
					statement.setInt(1, data.problemId);
					statement.setInt(2, data.genre);
					statement.setInt(3, data.type);
					statement.setString(4, data.sentence);
					for (int i = 0; i < 4; ++i) {
						statement.setString(5 + i, answer[i]);
						statement.setString(9 + i, choice[i]);
					}
					statement.setInt(13, data.good);
					statement.setInt(14, data.bad);
					statement.setString(15, data.creator);
					statement.setString(16, data.note);
					statement.setBoolean(17, data.imageAnswer);
					statement.setBoolean(18, data.imageChoice);
					statement.setInt(19, data.randomFlag);
					statement.executeUpdate();
					statement.close();

					return problemId;
				}
			});
		}
	}

	@Override
	public void updateProblemData(final PacketProblemData data) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final String[] answer = new String[4];
				final String[] choice = new String[4];
				for (int i = 0; i < 4; ++i) {
					answer[i] = choice[i] = "";

					if (data.answer != null && i < data.answer.length && data.answer[i] != null) {
						answer[i] = data.answer[i];
					}
					if (data.choice != null && i < data.choice.length && data.choice[i] != null) {
						choice[i] = data.choice[i];
					}
				}

				final PreparedStatement statement = connection.prepareStatement("UPDATE problem SET GENRE = ?, TYPE = ?, SENTENCE = ?, ANSWER0 = ?, ANSWER1 = ?, ANSWER2 = ?, ANSWER3 = ?, CHOICE0 = ?, CHOICE1 = ?, CHOICE2 = ?, CHOICE3 = ?, GOOD = ?, BAD = ?, CREATER = ?, NOTE = ?, IMAGE_ANSWER = ?, IMAGE_CHOICE = ?, RANDOM_FLAG = ?, VOTE_GOOD = ?, VOTE_BAD = ? WHERE ID = ?");
				statement.setInt(1, data.genre);
				statement.setInt(2, data.type);
				statement.setString(3, data.sentence);
				for (int i = 0; i < 4; ++i) {
					statement.setString(4 + i, answer[i]);
					statement.setString(8 + i, choice[i]);
				}
				statement.setInt(12, data.good);
				statement.setInt(13, data.bad);
				statement.setString(14, data.creator);
				statement.setString(15, data.note);
				statement.setBoolean(16, data.imageAnswer);
				statement.setBoolean(17, data.imageChoice);
				statement.setInt(18, data.randomFlag);
				statement.setInt(19, data.voteGood);
				statement.setInt(20, data.voteBad);
				statement.setInt(21, data.problemId);

				statement.executeUpdate();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public void updateMinimumProblemData(final PacketProblemDataMinimum data) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("UPDATE problem SET GENRE = ?, TYPE = ?, GOOD = ?, BAD = ?, RANDOM_FLAG = ? WHERE ID = ?");
				statement.setInt(1, data.genre);
				statement.setInt(2, data.type);
				statement.setInt(3, data.good);
				statement.setInt(4, data.bad);
				statement.setInt(5, data.randomFlag);
				statement.setInt(6, data.problemId);
				statement.executeUpdate();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public PacketProblemData[] getProblemData(final List<Integer> ids) {
		return doTask(new Task<PacketProblemData[]>() {
			@Override
			public PacketProblemData[] run(Connection connection) throws SQLException {
				final List<PacketProblemData> datas = new ArrayList<PacketProblemData>(Constant.MAX_PROBLEMS_PER_SESSION);

				final PreparedStatement statement = connection.prepareStatement("SELECT * FROM problem WHERE ID = ?");
				for (int id : ids) {
					statement.setInt(1, id);
					final ResultSet resultSet = statement.executeQuery();
					while (resultSet.next()) {
						final PacketProblemData data = new PacketProblemData();
						data.problemId = resultSet.getInt("ID");
						data.genre = resultSet.getInt("GENRE");
						data.type = resultSet.getInt("TYPE");
						data.sentence = resultSet.getString("SENTENCE");
						data.answer = new String[4];
						data.choice = new String[4];
						for (int i = 0; i < 4; ++i) {
							data.answer[i] = resultSet.getString("ANSWER" + i);
							data.choice[i] = resultSet.getString("CHOICE" + i);
						}
						data.good = resultSet.getInt("GOOD");
						data.bad = resultSet.getInt("BAD");
						data.creator = resultSet.getString("CREATER");
						data.note = resultSet.getString("NOTE");
						data.imageAnswer = resultSet.getBoolean("IMAGE_ANSWER");
						data.imageChoice = resultSet.getBoolean("IMAGE_CHOICE");
						data.randomFlag = resultSet.getInt("RANDOM_FLAG");
						data.voteGood = resultSet.getInt("VOTE_GOOD");
						data.voteBad = resultSet.getInt("VOTE_BAD");

						// この書き方馬鹿じゃね？
						final int permutations[][] = { Utility.getInstance().makePermutationArray(data.getNumberOfAnswer()), Utility.getInstance().makePermutationArray(data.getNumberOfChoice()) };
						data.createRandomizedData(permutations);

						datas.add(data);
					}
					resultSet.close();
				}
				statement.close();

				return datas.toArray(new PacketProblemData[0]);
			}
		});
	}

	@Override
	public PacketProblemData[] searchProblem(final String query, final String creater, int genre, int type, int randomFlag) {
		final boolean queryEmpty = (query == null || query.isEmpty());
		final boolean createrEmpty = (creater == null || creater.isEmpty());
		if (queryEmpty && createrEmpty && genre <= 1 && type <= 1) {
			return new PacketProblemData[0];
		}

		if (genre == 0 || (genre & 1) == 1) {
			genre = (1 << Constant.NUMBER_OF_GENRE) - 1;
		}
		if (type == 0 || (type & 1) == 1) {
			type = (1 << Constant.NUMBER_OF_TYPE) - 1;
		}
		if (randomFlag == 0 || (randomFlag & 1) == 1) {
			randomFlag = (1 << Constant.NUMBER_OF_RANDOM) - 1;
		}

		final StringBuilder b = new StringBuilder();
		b.append("SELECT * FROM problem WHERE ");

		// 問題文
		if (!queryEmpty) {
			b.append("MATCH (SENTENCE, ANSWER0, ANSWER1, ANSWER2, ANSWER3, CHOICE0, CHOICE1, CHOICE2, CHOICE3, NOTE) AGAINST (?) AND ");
		}

		// 作成者
		if (!createrEmpty) {
			b.append("MATCH (CREATER) AGAINST (?) AND ");
		}

		// ジャンル
		b.append("GENRE IN (");
		b.append(createCommaSeparated(genre, Constant.NUMBER_OF_GENRE));

		// 出題形式
		b.append(") AND TYPE IN (");
		b.append(createCommaSeparated(type, Constant.NUMBER_OF_TYPE_WITH_RANDOM));

		// ランダムフラグ
		b.append(") AND RANDOM_FLAG IN (");
		b.append(createCommaSeparated(randomFlag, Constant.NUMBER_OF_RANDOM));

		b.append(") ORDER BY ID ASC");

		return doTask(new Task<PacketProblemData[]>() {
			@Override
			public PacketProblemData[] run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement(b.toString());

				int index = 1;
				if (!queryEmpty) {
					statement.setString(index++, query);
				}
				if (!createrEmpty) {
					statement.setString(index++, creater);
				}

				return getPacketProblemDatas(statement);
			}
		});
	}

	private String createCommaSeparated(int flag, int length) {
		final StringBuilder b = new StringBuilder();
		boolean first = true;
		for (int index = 0; index < length; ++index) {
			if ((flag & (1 << index)) == 0) {
				continue;
			}

			if (first) {
				first = false;
			} else {
				b.append(',');
			}
			b.append(index);
		}
		return b.toString();
	}

	@Override
	public PacketProblemData[] searchSimilarProblemData(String query) {
		query = query.replaceAll("[\\p{ASCII}]", "");
		{
			// 文章を3文字づつ分解する
			final int length = query.length();
			if (length > 3) {
				final StringBuilder b = new StringBuilder(length);
				for (int i = 0; i + 3 <= length; ++i) {
					if (i != 0) {
						b.append(' ');
					}
					b.append(query.substring(i, i + 3));
				}
				query = b.toString();
			}
		}
		final String finalQuery = query;

		return doTask(new Task<PacketProblemData[]>() {
			@Override
			public PacketProblemData[] run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("SELECT * FROM problem WHERE MATCH (SENTENCE, ANSWER0, ANSWER1, ANSWER2, ANSWER3, CHOICE0, CHOICE1, CHOICE2, CHOICE3, NOTE) AGAINST (? IN BOOLEAN MODE) ORDER BY MATCH (SENTENCE, ANSWER0, ANSWER1, ANSWER2, ANSWER3, CHOICE0, CHOICE1, CHOICE2, CHOICE3, NOTE) AGAINST (? IN BOOLEAN MODE) DESC LIMIT 0, 10");
				statement.setString(1, finalQuery);
				statement.setString(2, finalQuery);
				return getPacketProblemDatas(statement);
			}
		});
	}

	@Override
	public PacketProblemData[] searchProblemDataByCreater(final String creater) {
		return doTask(new Task<PacketProblemData[]>() {
			@Override
			public PacketProblemData[] run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("SELECT * FROM problem WHERE MATCH (CREATER) AGAINST (?) ORDER BY ID ASC");
				statement.setString(1, creater);
				return getPacketProblemDatas(statement);
			}
		});
	}

	@Override
	public void addPlayerAnswers(final int problemID, final int type, final List<String> answers) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("INSERT DELAYED player_answer (PROBLEM_ID, ANSWER, COUNT) VALUES (?, ?, 1) ON DUPLICATE KEY UPDATE COUNT = COUNT + 1");
				statement.setInt(1, problemID);
				for (String answer : answers) {
					if (answer.isEmpty()) {
						continue;
					}

					// 線結びの回答整理
					if (type == Constant.TYPE_SENMUSUBI || type == Constant.TYPE_TATO) {
						final String[] split = answer.split(Constant.DELIMITER_GENERAL);
						Arrays.sort(split);

						final StringBuilder builder = new StringBuilder();
						for (String s : split) {
							builder.append(s);
							builder.append(Constant.DELIMITER_GENERAL);
						}
						while (builder.charAt(builder.length() - 1) == Constant.DELIMITER_GENERAL.charAt(0)) {
							builder.deleteCharAt(builder.length() - 1);
						}
						answer = builder.toString();
					}

					statement.setString(2, answer);
					statement.addBatch();
				}
				statement.executeBatch();
				statement.close();

				return null;
			}
		});
	}

	@Override
	public PacketWrongAnswer[] getPlayerAnswers(final int problemID) {
		return doTask(new Task<PacketWrongAnswer[]>() {
			@Override
			public PacketWrongAnswer[] run(Connection connection) throws SQLException {
				final List<PacketWrongAnswer> answers = new ArrayList<PacketWrongAnswer>();
				final PreparedStatement statement = connection.prepareStatement("SELECT ANSWER, COUNT FROM player_answer WHERE PROBLEM_ID = ? ORDER BY COUNT DESC");
				statement.setInt(1, problemID);
				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					final PacketWrongAnswer wrongAnswer = new PacketWrongAnswer();
					wrongAnswer.answer = resultSet.getString("ANSWER");
					wrongAnswer.count = resultSet.getInt("COUNT");
					answers.add(wrongAnswer);
				}
				resultSet.close();
				statement.close();
				return answers.toArray(new PacketWrongAnswer[0]);
			}
		});
	}

	@Override
	public void removePlayerAnswers(final int problemID) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("DELETE FROM player_answer WHERE PROBLEM_ID = ?");
				statement.setInt(1, problemID);
				statement.executeUpdate();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public PacketRankingData[][] getRankingData() {
		return doTask(new Task<PacketRankingData[][]>() {
			@Override
			public PacketRankingData[][] run(Connection connection) throws SQLException {
				final PacketRankingData[][] result = new PacketRankingData[Constant.NUMBER_OF_RANKING_TYPE][];
				result[Constant.RANKING_HIGH_SCORE] = getRankingDataFromResultSet(connection, "SELECT NAME, IMAGE_FILE_NAME, HIGH_SCORE AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?");
				result[Constant.RANKING_AVERAGE_SCORE] = getRankingDataFromResultSet(connection, "SELECT NAME, IMAGE_FILE_NAME, AVERAGE_SCORE AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?");
				result[Constant.RANKING_PLAY_COUNT] = getRankingDataFromResultSet(connection, "SELECT NAME, IMAGE_FILE_NAME, PLAY_COUNT AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?");
				result[Constant.RANKING_VICTORY_POINT] = getRankingDataFromResultSet(connection, "SELECT NAME, IMAGE_FILE_NAME, VICTORY_POINT AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?");
				result[Constant.RANKING_AVERAGE_RANK] = getRankingDataFromResultSet(connection, "SELECT NAME, IMAGE_FILE_NAME, AVERAGE_RANK AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA ASC LIMIT 0, ?");
				result[Constant.RANKING_CLASS] = getRankingDataFromResultSet(connection, "SELECT NAME, IMAGE_FILE_NAME, CLASS_LEVEL AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?");
				result[Constant.RANKING_CORRECT_RATIO] = getRankingDataFromResultSet(connection, "SELECT player.NAME, player.IMAGE_FILE_NAME, correct_count.GOOD / (correct_count.GOOD + correct_count.BAD) AS DATA FROM player, correct_count " + "WHERE player.USER_CODE = correct_count.USER_CODE AND correct_count.GENRE = 0 AND correct_count.TYPE = 0 AND player.PLAY_COUNT >= 10 AND player.LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?");
				return result;
			}
		});
	}

	@Override
	public Map<String, PacketRankingData[]> getThemeModeRankingData() {
		return doTask(new Task<Map<String, PacketRankingData[]>>() {
			@Override
			public Map<String, PacketRankingData[]> run(Connection connection) throws SQLException {
				final Map<String, PacketRankingData[]> result = new HashMap<String, PacketRankingData[]>();
				for (String theme : getThemeModeQuery().keySet()) {
					result.put(theme, getRankingDataFromResultSet(connection, "SELECT NAME, IMAGE_FILE_NAME, SCORE AS DATA FROM player, theme_mode_score WHERE theme_mode_score.theme like '" + theme + "' AND player.USER_CODE = theme_mode_score.USER_CODE AND player.PLAY_COUNT >= 10 AND player.LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?"));
				}
				return result;
			}
		});
	}

	private PacketRankingData[] getRankingDataFromResultSet(Connection connection, String sql) throws SQLException {
		final PreparedStatement statement = connection.prepareStatement(sql);
		final long time = System.currentTimeMillis() - (long) Constant.RANKING_DISPLAY_DAY * 24 * 60 * 60 * 1000;
		statement.setTimestamp(1, new Timestamp(time));
		statement.setInt(2, Constant.NUMBER_OF_RANKING_DATA);

		final ResultSet resultSet = statement.executeQuery();
		resultSet.last();
		final int numberOfResut = resultSet.getRow();
		resultSet.beforeFirst();

		final PacketRankingData[] rankingDatas = new PacketRankingData[numberOfResut];
		int i = 0;
		while (resultSet.next()) {
			final PacketRankingData rankingData = new PacketRankingData();
			rankingData.name = resultSet.getString("NAME");
			rankingData.imageFileName = resultSet.getString("IMAGE_FILE_NAME");
			rankingData.data = resultSet.getString("DATA");
			rankingDatas[i++] = rankingData;
		}
		resultSet.close();
		statement.close();

		return rankingDatas;
	}

	@Override
	public void addCreationLog(final int problemID, final int userCode, final String machineIP) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("INSERT DELAYED INTO creation_log (PROBLEM_ID, USER_CODE, DATE, MACHINE_IP) VALUES (?, ?, ?, ?)");
				statement.setInt(1, problemID);
				statement.setInt(2, userCode);
				statement.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
				statement.setString(4, machineIP);
				statement.executeUpdate();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public void addIgnoreUserCode(final int userCode, final int targetUserCode) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("INSERT INTO ignore_id (USER_CODE, TARGET_USER_CODE) VALUES (?, ?)");
				statement.setInt(1, userCode);
				statement.setInt(2, targetUserCode);
				statement.executeUpdate();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public void removeIgnoreUserCode(final int userCode, final int targetUserCode) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("DELETE FROM ignore_id WHERE USER_CODE = ? AND TARGET_USER_CODE = ?");
				statement.setInt(1, userCode);
				statement.setInt(2, targetUserCode);
				statement.executeUpdate();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public void addServerIgnoreUserCode(final int userCode) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("INSERT IGNORE INTO ignore_id (USER_CODE, TARGET_USER_CODE) VALUES (?, ?)");
				statement.setInt(1, 0);
				statement.setInt(2, userCode);
				statement.executeUpdate();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public Set<Integer> getServerIgnoreUserCode() {
		return doTask(new Task<Set<Integer>>() {
			@Override
			public Set<Integer> run(Connection connection) throws SQLException {
				final Set<Integer> userCodes = new HashSet<Integer>();
				final PreparedStatement statement = connection.prepareStatement("SELECT TARGET_USER_CODE FROM ignore_id WHERE USER_CODE = ?");
				statement.setInt(1, 0);
				final ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					userCodes.add(rs.getInt("TARGET_USER_CODE"));
				}
				rs.close();
				statement.close();
				return userCodes;
			}
		});
	}

	@Override
	public void addChatLog(final PacketChatData data) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("INSERT DELAYED IGNORE INTO chat_log (DATE, NAME, BODY, CLASS_LEVEL, USER_CODE, MACHINE_IP) VALUES (?, ?, ?, ?, ?, ?)");
				statement.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
				statement.setString(2, data.name);
				statement.setString(3, data.body);
				statement.setInt(4, data.classLevel);
				statement.setInt(5, data.userCode);
				statement.setString(6, data.machineIp);
				statement.executeUpdate();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public Set<Integer> getLimitedUserCodes() {
		return doTask(new Task<Set<Integer>>() {
			@Override
			public Set<Integer> run(Connection connection) throws SQLException {
				final Set<Integer> result = new HashSet<Integer>();
				final PreparedStatement statement = connection.prepareStatement("SELECT * FROM limited_user_code");
				final ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					result.add(rs.getInt("USER_CODE"));
				}
				rs.close();
				statement.close();
				return result;
			}
		});
	}

	@Override
	public Set<String> getLimitedIps() {
		return doTask(new Task<Set<String>>() {
			@Override
			public Set<String> run(Connection connection) throws SQLException {
				final Set<String> result = new HashSet<String>();
				final PreparedStatement statement = connection.prepareStatement("SELECT * FROM limited_ip");
				final ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					result.add(rs.getString("IP"));
				}
				rs.close();
				statement.close();
				return result;
			}
		});
	}

	@Override
	public PacketProblemCreationLog[] getProblemCreationHistory(final int problemId) {
		return doTask(new Task<PacketProblemCreationLog[]>() {
			@Override
			public PacketProblemCreationLog[] run(Connection connection) throws SQLException {
				final List<PacketProblemCreationLog> list = new ArrayList<PacketProblemCreationLog>();
				final PreparedStatement statement = connection.prepareStatement("SELECT player.NAME, creation_log.USER_CODE, creation_log.DATE, creation_log.MACHINE_IP FROM player, creation_log WHERE creation_log.PROBLEM_ID = ? AND creation_log.USER_CODE = player.USER_CODE");
				statement.setInt(1, problemId);
				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					PacketProblemCreationLog history = new PacketProblemCreationLog();
					history.name = resultSet.getString("player.NAME");
					history.userCode = resultSet.getInt("creation_log.USER_CODE");
					history.date = new Date(resultSet.getTimestamp("creation_log.DATE").getTime());
					history.ip = resultSet.getString("creation_log.MACHINE_IP");
					list.add(history);
				}
				resultSet.close();
				statement.close();

				return list.toArray(new PacketProblemCreationLog[0]);
			}
		});
	}

	@Override
	public PacketBbsThread[] getBbsThread(final int start, final int count) {
		return doTask(new Task<PacketBbsThread[]>() {
			@Override
			public PacketBbsThread[] run(Connection connection) throws SQLException {
				final List<PacketBbsThread> list = new ArrayList<PacketBbsThread>();
				final PreparedStatement statement = connection.prepareStatement("SELECT id, title FROM bbs_thread ORDER BY lastUpdate DESC LIMIT ?, ?");
				statement.setInt(1, start);
				statement.setInt(2, count);

				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					PacketBbsThread thread = new PacketBbsThread();
					thread.id = resultSet.getLong("id");
					thread.title = resultSet.getString("title");
					list.add(thread);
				}
				resultSet.close();
				statement.close();

				return list.toArray(new PacketBbsThread[0]);
			}
		});
	}

	@Override
	public PacketBbsResponse[] getBbsResponses(final int threadId, final int count) {
		return doTask(new Task<PacketBbsResponse[]>() {
			@Override
			public PacketBbsResponse[] run(Connection connection) throws SQLException {
				final List<PacketBbsResponse> list = new ArrayList<PacketBbsResponse>();

				// 最新コメントcount個
				PreparedStatement statement = connection.prepareStatement("SELECT * FROM bbs_response WHERE threadId = ? ORDER BY id DESC LIMIT 0, ?");
				statement.setInt(1, threadId);
				statement.setInt(2, count);
				ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					PacketBbsResponse response = new PacketBbsResponse();
					response.id = resultSet.getLong("id");
					response.threadId = resultSet.getLong("threadId");
					response.name = resultSet.getString("name");
					response.userCode = resultSet.getInt("userCode");
					response.machineIp = resultSet.getString("userCode");
					response.dispInfo = resultSet.getInt("dispInfo");
					response.postTime = resultSet.getLong("postTime");
					response.body = resultSet.getString("body");
					list.add(response);
				}
				resultSet.close();
				statement.close();

				// スレ立て時のコメント
				statement = connection.prepareStatement("SELECT * FROM bbs_response WHERE threadId = ? ORDER BY id ASC LIMIT 0, 1");
				statement.setInt(1, threadId);
				resultSet = statement.executeQuery();
				while (resultSet.next()) {
					PacketBbsResponse response = new PacketBbsResponse();
					response.id = resultSet.getLong("id");
					response.threadId = resultSet.getLong("threadId");
					response.name = resultSet.getString("name");
					response.userCode = resultSet.getInt("userCode");
					response.machineIp = resultSet.getString("userCode");
					response.dispInfo = resultSet.getInt("dispInfo");
					response.postTime = resultSet.getLong("postTime");
					response.body = resultSet.getString("body");
					if (list.get(list.size() - 1).postTime != response.postTime) {
						list.add(response);
					}
				}
				resultSet.close();
				statement.close();

				Collections.reverse(list);
				return list.toArray(new PacketBbsResponse[0]);
			}
		});
	}

	@Override
	public void buildBbsThread(final PacketBbsThread thread, final PacketBbsResponse response) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				PreparedStatement statement = connection.prepareStatement("INSERT INTO bbs_thread (lastUpdate, title) VALUES (?, ?)");
				statement.setLong(1, System.currentTimeMillis());
				statement.setString(2, thread.title);
				statement.executeUpdate();
				statement.close();

				statement = connection.prepareStatement("SELECT LAST_INSERT_ID() FROM bbs_thread");
				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					response.threadId = resultSet.getLong(1);
				}
				resultSet.close();
				statement.close();

				if (response.threadId == -1) {
					return null;
				}

				statement = connection.prepareStatement("INSERT INTO bbs_response (threadId, name, userCode, machineIp, dispInfo, postTime, body) VALUES (?, ?, ?, ?, ?, ?, ?)");
				statement.setLong(1, response.threadId);
				statement.setString(2, response.name);
				statement.setInt(3, response.userCode);
				statement.setString(4, response.machineIp);
				statement.setInt(5, response.dispInfo);
				statement.setLong(6, System.currentTimeMillis());
				statement.setString(7, response.body);
				statement.executeUpdate();
				statement.close();

				return null;
			}
		});
	}

	@Override
	public void writeToBbs(final PacketBbsResponse response, final boolean age) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				PreparedStatement statement = connection.prepareStatement("INSERT INTO bbs_response (threadId, name, userCode, machineIp, dispInfo, postTime, body) VALUES (?, ?, ?, ?, ?, ?, ?)");
				statement.setLong(1, response.threadId);
				statement.setString(2, response.name);
				statement.setInt(3, response.userCode);
				statement.setString(4, response.machineIp);
				statement.setInt(5, response.dispInfo);
				statement.setLong(6, System.currentTimeMillis());
				statement.setString(7, response.body);
				statement.executeUpdate();
				statement.close();

				if (age) {
					statement = connection.prepareStatement("UPDATE bbs_thread SET lastUpdate = ? WHERE id = ?");
					statement.setLong(1, System.currentTimeMillis());
					statement.setLong(2, response.threadId);
					statement.executeUpdate();
					statement.close();
				}

				return null;
			}
		});
	}

	@Override
	public int getNumberOfBbsThread() {
		return doTask(new Task<Integer>() {
			@Override
			public Integer run(Connection connection) throws SQLException {
				int result = 0;
				final PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM bbs_thread");
				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					result = resultSet.getInt(1);
				}
				resultSet.close();
				statement.close();
				return result;
			}
		});
	}

	@Override
	public boolean isUsedUserCode(final int userCode) {
		return doTask(new Task<Boolean>() {
			@Override
			public Boolean run(Connection connection) throws SQLException {
				boolean result = false;
				final PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM player WHERE USER_CODE = ?");
				statement.setInt(1, userCode);
				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					result = resultSet.getInt(1) != 0;
				}
				resultSet.close();
				statement.close();
				return result;
			}
		});
	}

	@Override
	public void addLinkData(final PacketLinkData linkData) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("INSERT INTO link (lastUpdate, homePageName, authorName, url, bannerUrl, description, userCode) VALUES (?, ?, ?, ?, ?, ?, ?)");
				statement.setLong(1, System.currentTimeMillis());
				statement.setString(2, linkData.homePageName);
				statement.setString(3, linkData.authorName);
				statement.setString(4, linkData.url);
				statement.setString(5, linkData.bannerUrl);
				statement.setString(6, linkData.description);
				statement.setInt(7, linkData.userCode);
				statement.execute();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public void updateLinkData(final PacketLinkData linkData) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("UPDATE link SET lastUpdate = ?, homePageName = ?, authorName = ?, url = ?, bannerUrl = ?, description = ?, userCode = ? WHERE id = ?");
				statement.setLong(1, System.currentTimeMillis());
				statement.setString(2, linkData.homePageName);
				statement.setString(3, linkData.authorName);
				statement.setString(4, linkData.url);
				statement.setString(5, linkData.bannerUrl);
				statement.setString(6, linkData.description);
				statement.setInt(7, linkData.userCode);
				statement.setInt(8, linkData.id);
				statement.execute();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public void removeLinkData(final int id) {
		doTask(new Task<Integer>() {
			@Override
			public Integer run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("UPDATE link SET valid = FALSE WHERE id = ?");
				statement.setInt(1, id);
				statement.execute();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public PacketLinkData[] getLinkDatas(final int start, final int count) {
		return doTask(new Task<PacketLinkData[]>() {
			@Override
			public PacketLinkData[] run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("SELECT * FROM link WHERE valid = TRUE ORDER BY lastUpdate DESC LIMIT ?, ?");
				statement.setInt(1, start);
				statement.setInt(2, count);
				final ResultSet resultSet = statement.executeQuery();
				final List<PacketLinkData> list = new ArrayList<PacketLinkData>();
				while (resultSet.next()) {
					PacketLinkData linkData = new PacketLinkData();
					linkData.id = (int) resultSet.getLong("id");
					linkData.lastUpdate = resultSet.getLong("lastUpdate");
					linkData.homePageName = resultSet.getString("homePageName");
					linkData.authorName = resultSet.getString("authorName");
					linkData.url = resultSet.getString("url");
					linkData.bannerUrl = resultSet.getString("bannerUrl");
					linkData.description = resultSet.getString("description");
					linkData.userCode = resultSet.getInt("userCode");
					list.add(linkData);
				}
				resultSet.close();
				statement.close();

				return list.toArray(new PacketLinkData[0]);
			}
		});
	}

	@Override
	public int getNumberOfLinkDatas() {
		return doTask(new Task<Integer>() {
			@Override
			public Integer run(Connection connection) throws SQLException {
				int result = 0;
				final PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM link WHERE valid = TRUE");
				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					result = resultSet.getInt(1);
				}
				resultSet.close();
				statement.close();
				return result;
			}
		});
	}

	@Override
	public int getNumberOfActiveUsers() {
		return doTask(new Task<Integer>() {

			@Override
			public Integer run(Connection connection) throws SQLException {
				int result = 0;
				final PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ?");
				final long time = System.currentTimeMillis() - (long) Constant.RANKING_DISPLAY_DAY * 24 * 60 * 60 * 1000;
				statement.setLong(1, time);
				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					result = resultSet.getInt(1);
				}
				resultSet.close();
				statement.close();
				return result;
			}
		});
	}

	@Override
	public Map<Integer, List<Integer>> getRatingGroupedByPrefecture() {
		return doTask(new Task<Map<Integer, List<Integer>>>() {
			@Override
			public Map<Integer, List<Integer>> run(Connection connection) throws SQLException {
				final Map<Integer, List<Integer>> result = new HashMap<Integer, List<Integer>>();
				final PreparedStatement statement = connection.prepareStatement("SELECT VICTORY_POINT, PREFECTURE FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? AND PREFECTURE != 0");
				final long time = System.currentTimeMillis() - (long) Constant.RANKING_DISPLAY_DAY * 24 * 60 * 60 * 1000;
				statement.setLong(1, time);

				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					final int rating = resultSet.getInt("VICTORY_POINT");
					final int prefecture = resultSet.getInt("PREFECTURE");

					if (!result.containsKey(prefecture)) {
						result.put(prefecture, new ArrayList<Integer>());
					}
					result.get(prefecture).add(rating);
				}
				resultSet.close();
				statement.close();
				return result;
			}
		});
	}

	@Override
	public void addRatingHistory(final int userCode, final int rating) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("INSERT INTO rating_history (USER_CODE, TIME, RATING) VALUES (?, ?, ?)");
				statement.setInt(1, userCode);
				statement.setLong(2, System.currentTimeMillis());
				statement.setInt(3, rating);
				statement.executeUpdate();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public int[] getRatingHistory(final int userCode) {
		return doTask(new Task<int[]>() {
			@Override
			public int[] run(Connection connection) throws SQLException {
				int[] result = null;
				final PreparedStatement statement = connection.prepareStatement("SELECT RATING FROM rating_history WHERE USER_CODE = ? ORDER BY TIME DESC LIMIT 0, ?");
				statement.setInt(1, userCode);
				statement.setInt(2, Constant.MAX_RATING_HISTORY);
				final ResultSet resultSet = statement.executeQuery();
				resultSet.last();
				result = new int[resultSet.getRow()];
				resultSet.beforeFirst();
				int index = 0;
				while (resultSet.next()) {
					result[index++] = resultSet.getInt("RATING");
				}
				resultSet.close();
				statement.close();
				return result;
			}
		});
	}

	@Override
	public int[] getWholeRating() {
		return doTask(new Task<int[]>() {
			@Override
			public int[] run(Connection connection) throws SQLException {
				int[] result = null;
				final PreparedStatement statement = connection.prepareStatement("SELECT VICTORY_POINT FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ?");
				final long time = System.currentTimeMillis() - (long) Constant.RANKING_DISPLAY_DAY * 24 * 60 * 60 * 1000;
				statement.setLong(1, time);
				final ResultSet resultSet = statement.executeQuery();
				resultSet.last();
				result = new int[resultSet.getRow()];
				resultSet.beforeFirst();
				int index = 0;
				while (resultSet.next()) {
					result[index++] = resultSet.getInt("VICTORY_POINT");
				}
				resultSet.close();
				statement.close();
				return result;
			}
		});
	}

	@Override
	public PacketProblemData[] getAdsenseProblems(final String query) {
		return doTask(new Task<PacketProblemData[]>() {
			@Override
			public PacketProblemData[] run(Connection connection) throws SQLException {
				final List<Integer> ids = new ArrayList<Integer>();
				final PreparedStatement statement = connection.prepareStatement("SELECT ID FROM problem WHERE MATCH (SENTENCE, ANSWER0, ANSWER1, ANSWER2, ANSWER3, CHOICE0, CHOICE1, CHOICE2, CHOICE3, NOTE) AGAINST (? IN BOOLEAN MODE)");
				statement.setString(1, query);
				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					ids.add(resultSet.getInt("ID"));
				}
				resultSet.close();
				statement.close();
				return getProblemData(ids);
			}
		});
	}

	@Override
	public Map<String, List<String>> getThemeModeQuery() {
		return doTask(new Task<Map<String, List<String>>>() {
			@Override
			public Map<String, List<String>> run(Connection connection) throws SQLException {
				final Map<String, List<String>> queries = new HashMap<String, List<String>>();
				final PreparedStatement statement = connection.prepareStatement("SELECT * FROM theme_mode");
				final ResultSet resultSet = statement.executeQuery();

				while (resultSet.next()) {
					final String theme = resultSet.getString("THEME");
					final String query = resultSet.getString("QUERY");

					if (!queries.containsKey(theme)) {
						queries.put(theme, new ArrayList<String>());
					}

					queries.get(theme).add(query);
				}

				resultSet.close();
				statement.close();
				return queries;
			}
		});
	}

	@Override
	public void addThemeModeQuery(final String theme, final String query) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("INSERT theme_mode (THEME, QUERY) VALUES (?, ?)");
				statement.setString(1, theme);
				statement.setString(2, query);
				statement.execute();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public void removeThemeModeQuery(final String theme, final String query) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("DELETE FROM theme_mode WHERE THEME LIKE ? AND QUERY LIKE ?");
				statement.setString(1, theme);
				statement.setString(2, query);
				statement.execute();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public void updateThemeModeScore(final int userCode, final String theme, final int score) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("INSERT LOW_PRIORITY theme_mode_score (THEME, USER_CODE, SCORE) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE SCORE = ((SCORE < ?) * ? + (SCORE > ?) * SCORE);");
				statement.setString(1, theme);
				statement.setInt(2, userCode);
				statement.setInt(3, score);
				statement.setInt(4, score);
				statement.setInt(5, score);
				statement.setInt(6, score);
				statement.executeUpdate();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public void voteToProblem(final int problemId, final boolean good, final String feedback) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final int voteGood = good ? 1 : 0;
				final int voteBad = good ? 0 : 1;

				PreparedStatement statement = connection.prepareStatement("UPDATE problem SET VOTE_GOOD = VOTE_GOOD + ?, VOTE_BAD = VOTE_BAD + ? WHERE ID = ?;");
				statement.setInt(1, voteGood);
				statement.setInt(2, voteBad);
				statement.setInt(3, problemId);
				statement.executeUpdate();
				statement.close();

				statement = connection.prepareStatement("INSERT problem_questionnaire (problemId, text, date) VALUES (?, ?, ?)");
				statement.setInt(1, problemId);
				statement.setString(2, feedback);
				statement.setLong(3, System.currentTimeMillis());
				statement.executeUpdate();
				statement.close();

				return null;
			}
		});
	}

	@Override
	public void clearProblemFeedback(final int problemId) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				PreparedStatement statement = connection.prepareStatement("UPDATE problem_questionnaire SET deleted = TRUE WHERE problemId = ?");
				statement.setInt(1, problemId);
				statement.executeUpdate();
				statement.close();
				return null;
			}
		});
	}

	@Override
	public List<String> getProblemFeedback(final int problemId) {
		return doTask(new Task<List<String>>() {
			@Override
			public List<String> run(Connection connection) throws SQLException {
				final List<String> result = new ArrayList<String>();
				PreparedStatement statement = connection.prepareStatement("SELECT * FROM problem_questionnaire WHERE problemId = ? && deleted = FALSE");
				statement.setInt(1, problemId);
				ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					result.add(resultSet.getString("text"));
				}
				resultSet.close();
				statement.close();
				return result;
			}
		});
	}

	private static final String[] sentenceKeys = { "SENTENCE", "ANSWER0", "ANSWER1", "ANSWER2", "ANSWER3", "CHOICE0", "CHOICE1", "CHOICE2", "CHOICE3" };

	@Override
	public void processProblems(final SentencesProcessor sentencesProcessor) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final StringBuilder sb = new StringBuilder();
				sb.append("SELECT ID");
				for (int i = 0; i < sentenceKeys.length; ++i) {
					sb.append(",");
					sb.append(sentenceKeys[i]);
				}
				sb.append(" FROM problem");

				final PreparedStatement statement = connection.prepareStatement(sb.toString());
				final ResultSet resultSet = statement.executeQuery();
				int counter = 0;
				final StringBuilder sentence = new StringBuilder();
				while (resultSet.next()) {
					sentence.delete(0, Integer.MAX_VALUE);

					final int problemId = resultSet.getInt("ID");
					for (String sentenceKey : sentenceKeys) {
						final String sentenceValue = resultSet.getString(sentenceKey);
						if (sentenceValue == null || sentenceValue.isEmpty()) {
							continue;
						}
						sentence.append(sentenceValue).append(" ");
					}

					try {
						sentencesProcessor.process(sentence.toString(), problemId);
					} catch (Exception e) {
					}

					if (++counter % 10000 == 0) {
						System.err.println(counter);
					}
				}
				return null;
			}
		});
	}

	@Override
	public PacketProblemData[] getLastestProblems() {
		final List<Integer> problemIds = doTask(new Task<List<Integer>>() {
			@Override
			public List<Integer> run(Connection connection) throws SQLException {
				final List<Integer> problemIds = new ArrayList<Integer>();
				final PreparedStatement statement = connection.prepareStatement("SELECT ID FROM problem ORDER BY ID DESC LIMIT 0, 100");
				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					problemIds.add(resultSet.getInt("ID"));
				}
				resultSet.close();
				statement.close();
				return problemIds;
			}
		});

		final List<PacketProblemData> problemDatas = new ArrayList<PacketProblemData>(Arrays.asList(getProblemData(problemIds)));
		Collections.sort(problemDatas, comparatorByProblemId);
		return problemDatas.toArray(new PacketProblemData[0]);
	}

	private final Comparator<PacketProblemData> comparatorByProblemId = new Comparator<PacketProblemData>() {
		@Override
		public int compare(PacketProblemData o1, PacketProblemData o2) {
			return o2.problemId - o1.problemId;
		}
	};

	@Override
	public Map<Integer, Integer> getThemeModeEditorsStatus() {
		return doTask(new Task<Map<Integer, Integer>>() {
			@Override
			public Map<Integer, Integer> run(Connection connection) throws SQLException {
				final Map<Integer, Integer> result = new HashMap<Integer, Integer>();

				final PreparedStatement statement = connection.prepareStatement("SELECT * FROM theme_mode_editor");
				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					final int userCode = resultSet.getInt("userCode");
					final int status = resultSet.getInt("status");
					result.put(userCode, status);
				}
				resultSet.close();
				statement.close();

				return result;
			}
		});
	}

	@Override
	public void updateThemeModeEdtorsStatus(final int userCode, final int status) {
		doTask(new Task<Void>() {
			@Override
			public Void run(Connection connection) throws SQLException {
				final PreparedStatement statement = connection.prepareStatement("REPLACE theme_mode_editor (userCode, status) VALUES (?, ?)");
				statement.setInt(1, userCode);
				statement.setInt(2, status);
				statement.executeUpdate();
				statement.close();
				return null;
			}
		});
	}
}
